<?php

class Application_Model_Students extends Zend_Db_Table_Abstract
{

    protected $_name = "Students";
    protected $_primary = "id";
    protected $db;

    public function  __construct() {
        parent::__construct();
        $this->db=Zend_Registry::get('db');
    }

    public function listStu()
    {
        $sql = $this->db->query("select * from Students where id='40'");
        return $sql->fetchAll();
    }

    public function listAllForAdmin()
    {
            $query=$this->select();
            $query->from($this->_name);
            $query->order('created');
            return $query;
    }
    
    //find all student in db for show all function
    public function listall()
    {
            $query=$this->select();
            $query->from($this->_name);
            $query->where('status=?',0);
            $query->where('priviledge=?',0);
            $query->order('updated DESC');
            return $query;
    }
    //find student by id for show student's details
    public function findStudentById($studentid)
    {
        $row = $this->find($studentid)->current();
        return $row;

    }
    //find student by email for forget and introduce friend functions
    public function findStudentByEmail($email)
    {
        $query = $this->select();
        $query->from($this->_name);
        $query->where('email like ?',$email);
        $data = $this->fetchRow($query);
        
        return $data;
    }
    //filter students by school, class, gender
    public function listStudents($school,$class, $gender)
    {
        $query=$this->select();
        $query->from($this->_name);
        if(strcmp($school, "all") == 0)
        {
            $query->where('school like ?','%');
        }
        else
        {
            $query->where('school like ?',$school);
        }
        if( $class == 0)
        {

        }
        else
        {
            $query->where('fromclass <= ?',$class);
            $query->where('toclass >= ?',$class);
        }
        if(strcmp($gender, "all") == 0)
        {
            $query->where('gender like ?','%');
        }
        else
        {
            $query->where('gender like ?',$gender);
        }
        $query->where('status = ?',0);
        $query->where('priviledge=?',0);
        $query->order('updated DESC');
        
        return $query;
    }

    public function countStudents()
    {
        $query = $this->select();
        $query->from($this->_name, 'count(*) as totalstudents');
        $query->where('priviledge=?',0);
        $data = $this->fetchRow($query);
        
        return $data;
    }

    public function countTeached()
    {
        $query = $this->select();
        $query->from($this->_name, 'count(*) as totalteached');
        $query->where('status like ?', '1');
        $query->where('priviledge=?',0);
        $data = $this->fetchRow($query)->totalteached;
//        $sql = $this->db->query("select count(*) as totalteached from Students");

//        return $sql->fetchAll()->totalteached;
        return $data;
    }

    public function countStudentsBySchool($school)
    {
        $query = $this->select();
        $query->from($this->_name, 'count(*) as students');
        $query->where('school like ?', $school);
        $data = $this->fetchRow($query)->students;
        return $data;
    }
    public function getInterval($studentId)
    {
        $sql = $this->db->query("select timediff(now(), updated) as intervalTime from Students where id=".$studentId);
        return $sql->fetchAll();
    }

}

